SET QUOTED_IDENTIFIER OFF;
SET ANSI_NULLS ON;
SET NOCOUNT ON;
GO

IF EXISTS (	SELECT *
		FROM INFORMATION_SCHEMA.ROUTINES
		WHERE ROUTINE_NAME = 'GetPagedInventory'
			AND ROUTINE_TYPE = 'PROCEDURE' )
BEGIN
	PRINT 'Dropping stored procedure GetPagedInventory.';
	DROP PROCEDURE [dbo].[GetPagedInventory];
END

GO
-------------------------------------------------------------------------------
--	METADATA AND OTHER MESSAGES ABOVE.
--	BEGIN CREATE STATEMENTS.
-------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[GetPagedInventory]	
	@CompanyID AS INT
	, @StartRecord AS INT = 1
	, @EndRecord AS INT = 10
	, @Type As VARCHAR(50)
	, @IsPosted As BIT
AS

BEGIN	
	SET NOCOUNT ON;
IF(@Type = 'Issue')
BEGIN
    WITH CategoryProducts AS
	(
		SELECT
		T.ID AS [Id]
		,TransactionDate AS [DATE]
		,S.[Address] AS [Store]
		,T.ReferenceNo AS [ReferenceNo]
		,T.[Description] AS [Description]
		,ROW_NUMBER() OVER (ORDER BY T.TransactionDate DESC) AS [RowNumber]
		FROM [Transaction] T
		INNER JOIN Store S ON T.StoreID = S.ID
		WHERE S.CompanyID = @CompanyID
		AND T.IsIssue = 1
		AND T.IsPosted =@IsPosted
	)
	SELECT *		
		, (SELECT TOP 1 ProductImage.Thumbnail FROM ProductImage WHERE ProductImage.ProductID = CategoryProducts.ID) AS Thumbnail
		, (SELECT MAX(RowNumber) FROM CategoryProducts) AS TotalRecord
	FROM CategoryProducts
	WHERE [RowNumber] BETWEEN @StartRecord AND @EndRecord
END

ELSE
BEGIN
	WITH CategoryProducts AS
	(
		SELECT
		T.ID AS [Id]
		,TransactionDate AS [DATE]
		,S.[Address] AS [Store]
		,T.ReferenceNo AS [ReferenceNo]
		,T.[Description] AS [Description]
		,ROW_NUMBER() OVER (ORDER BY T.TransactionDate DESC) AS [RowNumber]
		FROM [Transaction] T
		INNER JOIN Store S ON T.StoreID = S.ID
		WHERE S.CompanyID = @CompanyID
		AND T.IsReceive = 1	
		AND T.IsPosted =@IsPosted	
	)
	SELECT *		
		, (SELECT TOP 1 ProductImage.Thumbnail FROM ProductImage WHERE ProductImage.ProductID = CategoryProducts.ID) AS Thumbnail
		, (SELECT MAX(RowNumber) FROM CategoryProducts) AS TotalRecord
	FROM CategoryProducts
	WHERE [RowNumber] BETWEEN @StartRecord AND @EndRecord
END


END
GO

-------------------------------------------------------------------------------
--	END OF CREATE STATEMENTS.
--	METADATA AND SUCCESS/ERROR MESSAGES BELOW.
-------------------------------------------------------------------------------
IF EXISTS (	SELECT *
			FROM INFORMATION_SCHEMA.ROUTINES
			WHERE ROUTINE_NAME = 'GetPagedInventory'
				AND ROUTINE_TYPE = 'PROCEDURE' )
BEGIN
	PRINT 'Stored procedure GetPagedInventory created successfully.';	
END
ELSE
BEGIN
	PRINT 'ERROR: Failed to create stored procedure GetPagedInventory.';
END
GO



